Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Procedures

A procedure is a set of PL/SQL statements that form a subprogram. The subprogram is designed and created to perform a specific operation on data in your database. A procedure takes zero or more input parameters and returns zero or more output parameters. The syntax of a procedure is as follows:

PROCEDURE procedure_name [( parameter_declaration )] IS
    [ local declarations ]
BEGIN
   PL/SQL Statements
[ EXCEPTION
      Optional Exception Handler(s) ]
END [ procedure_name ];

In this syntax, the parameter_declaration has the following format:

parameter_name [ IN | OUT | IN OUT ] datatype

The parameter qualifiers have the following meanings:


Qualifier Description

IN This parameter is used as an input value only.
OUT This parameter is used as an output value only.
IN OUT This parameter is used as both an input and an output variable.

The procedure is made up of two parts: the declaration and the body of the procedure. The declaration begins with the keyword PROCEDURE and ends with the last parameter declaration. The body begins with the keyword IS and ends with the keyword END.

The declaration part is used to define which variables are passed to the procedure and which values are returned from the procedure back to the calling program. The body of the procedure is where the real work is done. The body is made up of the PL/SQL statements that perform the desired task.

Functions

A function is a set of PL/SQL statements that form a subprogram. The subprogram is designed and created to perform a specific operation on data in your database. A function takes zero or more input parameters and returns one output value. If more than one output value is required, a procedure should be used. The syntax of a function is as follows:

FUNCTION function_name [( parameter_declaration )] RETURN datatype IS
    [ local declarations ]
BEGIN
    PL/SQL Statements
[ EXCEPTION
      Optional Exception Handler(s) ]
END [ function_name ];

The parameter_declaration has the same format as it does with a procedure:

parameter_name [ IN | OUT | IN OUT ] datatype

The parameter qualifiers have the following meanings:


Qualifier Description

IN This parameter is used as an input value only.
OUT This parameter is used as an output value only.
IN OUT This parameter is used as both an input and an output variable.

As with the procedure, the function is made up of two parts: the declaration and the body. The declaration begins with the keyword FUNCTION and ends with RETURN statement. The body begins with the keyword IS and ends with the keyword END.

The declaration part is used to define which variables are passed to the function and which values are returned from the function back to the calling program. The body of the function is where the real work is done. The body is made up of the PL/SQL statements that perform the desired task.

The difference between a procedure and a function is the return value. A function has the return declaration as well as a RETURN function within the body of the function that returns a value. This RETURN function is used to pass a return value to the calling program. If you do not intend to return a value to the calling program, or you want to return more than one value, use a procedure.


NOTE:  For the remainder of this chapter, the term procedure is used to refer to both procedures and functions.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.